
/* ---------------------------------------------------------------------- */
/* sf_guard_group											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_group')
BEGIN
	 DECLARE @reftable_38 nvarchar(60), @constraintname_38 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_group'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_38, @constraintname_38
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_38+' drop constraint '+@constraintname_38)
	   FETCH NEXT from refcursor into @reftable_38, @constraintname_38
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_group]
END


CREATE TABLE [sf_guard_group]
(
	[id] INT  NOT NULL IDENTITY,
	[name] VARCHAR(255)  NOT NULL,
	[description] VARCHAR(MAX)  NULL,
	CONSTRAINT [sf_guard_group_PK] PRIMARY KEY ([id]),
	UNIQUE ([name])
);

/* ---------------------------------------------------------------------- */
/* sf_guard_permission											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_permission')
BEGIN
	 DECLARE @reftable_39 nvarchar(60), @constraintname_39 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_permission'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_39, @constraintname_39
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_39+' drop constraint '+@constraintname_39)
	   FETCH NEXT from refcursor into @reftable_39, @constraintname_39
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_permission]
END


CREATE TABLE [sf_guard_permission]
(
	[id] INT  NOT NULL IDENTITY,
	[name] VARCHAR(255)  NOT NULL,
	[description] VARCHAR(MAX)  NULL,
	CONSTRAINT [sf_guard_permission_PK] PRIMARY KEY ([id]),
	UNIQUE ([name])
);

/* ---------------------------------------------------------------------- */
/* sf_guard_group_permission											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_group_permission_FK_1')
	ALTER TABLE [sf_guard_group_permission] DROP CONSTRAINT [sf_guard_group_permission_FK_1];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_group_permission_FK_2')
	ALTER TABLE [sf_guard_group_permission] DROP CONSTRAINT [sf_guard_group_permission_FK_2];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_group_permission')
BEGIN
	 DECLARE @reftable_40 nvarchar(60), @constraintname_40 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_group_permission'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_40, @constraintname_40
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_40+' drop constraint '+@constraintname_40)
	   FETCH NEXT from refcursor into @reftable_40, @constraintname_40
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_group_permission]
END


CREATE TABLE [sf_guard_group_permission]
(
	[group_id] INT  NOT NULL,
	[permission_id] INT  NOT NULL,
	CONSTRAINT [sf_guard_group_permission_PK] PRIMARY KEY ([group_id],[permission_id])
);

BEGIN
ALTER TABLE [sf_guard_group_permission] ADD CONSTRAINT [sf_guard_group_permission_FK_1] FOREIGN KEY ([group_id]) REFERENCES [sf_guard_group] ([id]) ON DELETE CASCADE
END
;

BEGIN
ALTER TABLE [sf_guard_group_permission] ADD CONSTRAINT [sf_guard_group_permission_FK_2] FOREIGN KEY ([permission_id]) REFERENCES [sf_guard_permission] ([id]) ON DELETE CASCADE
END
;

/* ---------------------------------------------------------------------- */
/* sf_guard_user											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_user')
BEGIN
	 DECLARE @reftable_41 nvarchar(60), @constraintname_41 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_user'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_41, @constraintname_41
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_41+' drop constraint '+@constraintname_41)
	   FETCH NEXT from refcursor into @reftable_41, @constraintname_41
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_user]
END


CREATE TABLE [sf_guard_user]
(
	[id] INT  NOT NULL IDENTITY,
	[username] VARCHAR(128)  NOT NULL,
	[algorithm] VARCHAR(128) default 'sha1' NOT NULL,
	[salt] VARCHAR(128)  NOT NULL,
	[password] VARCHAR(128)  NOT NULL,
	[created_at] DATETIME  NULL,
	[last_login] DATETIME  NULL,
	[is_active] INT default 1 NOT NULL,
	[is_super_admin] INT default 0 NOT NULL,
	CONSTRAINT [sf_guard_user_PK] PRIMARY KEY ([id]),
	UNIQUE ([username])
);

/* ---------------------------------------------------------------------- */
/* sf_guard_user_permission											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_user_permission_FK_1')
	ALTER TABLE [sf_guard_user_permission] DROP CONSTRAINT [sf_guard_user_permission_FK_1];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_user_permission_FK_2')
	ALTER TABLE [sf_guard_user_permission] DROP CONSTRAINT [sf_guard_user_permission_FK_2];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_user_permission')
BEGIN
	 DECLARE @reftable_42 nvarchar(60), @constraintname_42 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_user_permission'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_42, @constraintname_42
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_42+' drop constraint '+@constraintname_42)
	   FETCH NEXT from refcursor into @reftable_42, @constraintname_42
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_user_permission]
END


CREATE TABLE [sf_guard_user_permission]
(
	[user_id] INT  NOT NULL,
	[permission_id] INT  NOT NULL,
	CONSTRAINT [sf_guard_user_permission_PK] PRIMARY KEY ([user_id],[permission_id])
);

BEGIN
ALTER TABLE [sf_guard_user_permission] ADD CONSTRAINT [sf_guard_user_permission_FK_1] FOREIGN KEY ([user_id]) REFERENCES [sf_guard_user] ([id]) ON DELETE CASCADE
END
;

BEGIN
ALTER TABLE [sf_guard_user_permission] ADD CONSTRAINT [sf_guard_user_permission_FK_2] FOREIGN KEY ([permission_id]) REFERENCES [sf_guard_permission] ([id]) ON DELETE CASCADE
END
;

/* ---------------------------------------------------------------------- */
/* sf_guard_user_group											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_user_group_FK_1')
	ALTER TABLE [sf_guard_user_group] DROP CONSTRAINT [sf_guard_user_group_FK_1];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_user_group_FK_2')
	ALTER TABLE [sf_guard_user_group] DROP CONSTRAINT [sf_guard_user_group_FK_2];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_user_group')
BEGIN
	 DECLARE @reftable_43 nvarchar(60), @constraintname_43 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_user_group'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_43, @constraintname_43
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_43+' drop constraint '+@constraintname_43)
	   FETCH NEXT from refcursor into @reftable_43, @constraintname_43
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_user_group]
END


CREATE TABLE [sf_guard_user_group]
(
	[user_id] INT  NOT NULL,
	[group_id] INT  NOT NULL,
	CONSTRAINT [sf_guard_user_group_PK] PRIMARY KEY ([user_id],[group_id])
);

BEGIN
ALTER TABLE [sf_guard_user_group] ADD CONSTRAINT [sf_guard_user_group_FK_1] FOREIGN KEY ([user_id]) REFERENCES [sf_guard_user] ([id]) ON DELETE CASCADE
END
;

BEGIN
ALTER TABLE [sf_guard_user_group] ADD CONSTRAINT [sf_guard_user_group_FK_2] FOREIGN KEY ([group_id]) REFERENCES [sf_guard_group] ([id]) ON DELETE CASCADE
END
;

/* ---------------------------------------------------------------------- */
/* sf_guard_remember_key											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='sf_guard_remember_key_FK_1')
	ALTER TABLE [sf_guard_remember_key] DROP CONSTRAINT [sf_guard_remember_key_FK_1];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'sf_guard_remember_key')
BEGIN
	 DECLARE @reftable_44 nvarchar(60), @constraintname_44 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'sf_guard_remember_key'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_44, @constraintname_44
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_44+' drop constraint '+@constraintname_44)
	   FETCH NEXT from refcursor into @reftable_44, @constraintname_44
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [sf_guard_remember_key]
END


CREATE TABLE [sf_guard_remember_key]
(
	[user_id] INT  NOT NULL,
	[remember_key] VARCHAR(32)  NULL,
	[ip_address] VARCHAR(50)  NOT NULL,
	[created_at] DATETIME  NULL,
	CONSTRAINT [sf_guard_remember_key_PK] PRIMARY KEY ([user_id],[ip_address])
);

BEGIN
ALTER TABLE [sf_guard_remember_key] ADD CONSTRAINT [sf_guard_remember_key_FK_1] FOREIGN KEY ([user_id]) REFERENCES [sf_guard_user] ([id]) ON DELETE CASCADE
END
;
